package com.code.utils;

import com.code.TableAttribute;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static java.sql.DriverManager.getConnection;

/**
 * s数据库连接公共类
 */
public class DbUtils {

    private static Logger logger = Logger.getLogger(GenerateUtils.class);

    public static Connection getDbConnection(String driver, String url, String username, String pwd) {
        try {
            Class.forName(driver); // 加载数据库驱动程序
            // 连接数据库
            return getConnection(url, username, pwd);
        } catch (ClassNotFoundException | SQLException e) {
            logger.error(e.getMessage(), e);
        }
        return null;
    }

    public static List<TableAttribute> queryTableStructure(Connection connect, String tableName) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.append(" SELECT DISTINCT A.TABLE_NAME,(SELECT t.COMMENTS FROM User_Tab_Comments t WHERE t.TABLE_NAME = a.TABLE_NAME) TABLE_COMMENTS, ");
            sqlBuilder.append("  A.COLUMN_NAME, A.COLUMN_ID,A.DATA_TYPE,C.COMMENTS COLUMN_DESC ");
            sqlBuilder.append("    FROM ALL_TAB_COLUMNS A  JOIN ALL_COL_COMMENTS C  ON A.TABLE_NAME = C.TABLE_NAME  AND A.COLUMN_NAME = C.COLUMN_NAME ");
            sqlBuilder.append(" WHERE A.TABLE_NAME  IN  ( ").append(CodeUtils.formatterInCondition(tableName)).append(")");
            sqlBuilder.append("  ORDER BY TABLE_NAME,A.COLUMN_ID ");
            preparedStatement = connect.prepareStatement(sqlBuilder.toString());
            resultSet = preparedStatement.executeQuery();
            List<TableAttribute> tableAttributeList = new ArrayList<>();
            while (resultSet.next()) {
                String table = resultSet.getString("TABLE_NAME");
                String tableComments = resultSet.getString("TABLE_COMMENTS");
                String columnName = resultSet.getString("COLUMN_NAME");
                String columnDesc = resultSet.getString("COLUMN_DESC");
                String dataType = resultSet.getString("DATA_TYPE");
                TableAttribute tableAttribute = new TableAttribute(table, tableComments, columnName, dataType,columnDesc );
                tableAttributeList.add(tableAttribute);
            }
             return tableAttributeList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(connect, preparedStatement, resultSet);
        }
        return new ArrayList<>();
    }

    /**
     * 释放资源
     *
     * @param conn
     * @param ps
     * @param rs
     */
    private static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
